# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql
import logging
from itemadapter import ItemAdapter


class DangdangSpiderPipeline:
    def __init__(self):
        self.connection = None
        self.cursor = None
        
    def open_spider(self, spider):
        """连接数据库"""
        try:
            self.connection = pymysql.connect(
                host='localhost',
                user='root',
                password='123456',
                database='dangdang_db',
                charset='utf8mb4',
                autocommit=True
            )
            self.cursor = self.connection.cursor()
            logging.info("数据库连接成功")
        except Exception as e:
            logging.error(f"数据库连接失败: {e}")
            
    def close_spider(self, spider):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        logging.info("数据库连接已关闭")
        
    def process_item(self, item, spider):
        """处理商品数据"""
        try:
            adapter = ItemAdapter(item)
            
            # 准备插入数据 - 使用INSERT IGNORE，忽略重复的SKU
            sql = """
            INSERT IGNORE INTO products (
                sku, name, price, original_price, author, publisher, publish_date,
                image_url, description, comments, category1, category2, category3, link
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """
            
            values = (
                adapter.get('sku', ''),
                adapter.get('name', ''),
                adapter.get('price', 0.0),
                adapter.get('original_price', 0.0),
                adapter.get('author', ''),
                adapter.get('publisher', ''),
                adapter.get('publish_date', None),
                adapter.get('image_url', ''),
                adapter.get('description', ''),
                adapter.get('comments', 0),
                adapter.get('category1', ''),
                adapter.get('category2', ''),
                adapter.get('category3', ''),
                adapter.get('link', '')
            )
            
            self.cursor.execute(sql, values)
            logging.info(f"成功保存商品: {adapter.get('name', '')[:50]}...")
            
        except Exception as e:
            logging.error(f"保存商品失败: {e}")
            
        return item
